-- tracelog table
------------------------------------------------------------
if exists (select * from sys.tables where [name]='tracelog')
begin                  
    print 'table tracelog already exists.'
end
else
begin
    print 'creating table tracelog...'
 
    create table [dbo].[tracelog](
        [traceid] [bigint] identity(1,1) not null,
        [message] [xml] null,
        [type] [varchar](20) null default ('Verbose'),
        [loggeddate] [datetime] null default (getdate()),
    primary key clustered 
    (
        [traceid] asc
    )with (ignore_dup_key = off) on [primary]
    ) on [primary]
 
    print 'table tracelog created.';
end
go
 
-- stored procedure to write trace log entry
------------------------------------------------------------
if  exists (select * from sys.objects where object_id = object_id(N'[dbo].[prc_writetraceentry]') and type in (N'p', N'pc'))
    drop procedure [dbo].[prc_writetraceentry]
go
 
create proc prc_writetraceentry
    @message xml = null,
    @type varchar(100) = 'Verbose'
as
begin
    insert tracelog([message], type)
    values(@message, @type)
end
go
 
-- add index to speed up data retrieval in case the table 
-- gets too big
------------------------------------------------------------
create nonclustered index [idx_tracelog_loggeddate] on [dbo].[tracelog] 
(
    [loggeddate] desc,
    [traceid] asc
)
go